﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;

namespace Human
{
	public class frm_bangchamcong : System.Windows.Forms.Form
	{
		private AccessData mDB =new AccessData();
        Language lan = new Language();
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Button btn_chon;
		private System.Windows.Forms.Button btn_thoat;
		private System.Windows.Forms.TextBox txtthang;
		private System.Windows.Forms.TextBox txtnam;
		private string s_bophan="",user_dn="", thang="",nam="",s_mmyy="",ngay_ht="";
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.ComboBox cbobophan;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.RadioButton radBC;
		private System.Windows.Forms.RadioButton radKhac;

        private DataSet dscctt = new DataSet();       
        private Button btn_exel;
		private System.ComponentModel.Container components = null;

		public frm_bangchamcong(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
		}

    	protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frm_bangchamcong));
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.btn_chon = new System.Windows.Forms.Button();
            this.btn_thoat = new System.Windows.Forms.Button();
            this.txtthang = new System.Windows.Forms.TextBox();
            this.txtnam = new System.Windows.Forms.TextBox();
            this.label3 = new System.Windows.Forms.Label();
            this.cbobophan = new System.Windows.Forms.ComboBox();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.radKhac = new System.Windows.Forms.RadioButton();
            this.radBC = new System.Windows.Forms.RadioButton();
            this.btn_exel = new System.Windows.Forms.Button();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.ForeColor = System.Drawing.Color.DarkBlue;
            this.label1.Location = new System.Drawing.Point(22, 17);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(56, 23);
            this.label1.TabIndex = 4;
            this.label1.Text = "Tháng :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label2
            // 
            this.label2.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label2.ForeColor = System.Drawing.Color.DarkBlue;
            this.label2.Location = new System.Drawing.Point(142, 17);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(40, 23);
            this.label2.TabIndex = 5;
            this.label2.Text = "Năm :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // btn_chon
            // 
            this.btn_chon.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_chon.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_chon.Image = global::Human.Properties.Resources.t_search;
            this.btn_chon.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_chon.Location = new System.Drawing.Point(25, 144);
            this.btn_chon.Name = "btn_chon";
            this.btn_chon.Size = new System.Drawing.Size(60, 28);
            this.btn_chon.TabIndex = 2;
            this.btn_chon.Text = "&Chọn";
            this.btn_chon.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_chon.Click += new System.EventHandler(this.btn_chon_Click);
            // 
            // btn_thoat
            // 
            this.btn_thoat.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_thoat.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_thoat.Image = global::Human.Properties.Resources.close_r;
            this.btn_thoat.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_thoat.Location = new System.Drawing.Point(157, 144);
            this.btn_thoat.Name = "btn_thoat";
            this.btn_thoat.Size = new System.Drawing.Size(80, 28);
            this.btn_thoat.TabIndex = 3;
            this.btn_thoat.Text = "&Kết thúc";
            this.btn_thoat.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_thoat.Click += new System.EventHandler(this.btn_thoat_Click);
            // 
            // txtthang
            // 
            this.txtthang.Location = new System.Drawing.Point(84, 17);
            this.txtthang.Name = "txtthang";
            this.txtthang.Size = new System.Drawing.Size(40, 21);
            this.txtthang.TabIndex = 0;
            this.txtthang.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtthang_KeyDown);
            // 
            // txtnam
            // 
            this.txtnam.Location = new System.Drawing.Point(190, 17);
            this.txtnam.Name = "txtnam";
            this.txtnam.Size = new System.Drawing.Size(56, 21);
            this.txtnam.TabIndex = 1;
            this.txtnam.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtnam_KeyDown);
            // 
            // label3
            // 
            this.label3.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.ForeColor = System.Drawing.Color.DarkBlue;
            this.label3.Location = new System.Drawing.Point(10, 51);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(68, 23);
            this.label3.TabIndex = 6;
            this.label3.Text = "Bộ phận :";
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // cbobophan
            // 
            this.cbobophan.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.cbobophan.ForeColor = System.Drawing.Color.Navy;
            this.cbobophan.Location = new System.Drawing.Point(84, 53);
            this.cbobophan.Name = "cbobophan";
            this.cbobophan.Size = new System.Drawing.Size(162, 22);
            this.cbobophan.TabIndex = 7;
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.radKhac);
            this.groupBox1.Controls.Add(this.radBC);
            this.groupBox1.Location = new System.Drawing.Point(18, 86);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(233, 47);
            this.groupBox1.TabIndex = 8;
            this.groupBox1.TabStop = false;
            // 
            // radKhac
            // 
            this.radKhac.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radKhac.ForeColor = System.Drawing.Color.DarkBlue;
            this.radKhac.Location = new System.Drawing.Point(102, 14);
            this.radKhac.Name = "radKhac";
            this.radKhac.Size = new System.Drawing.Size(120, 24);
            this.radKhac.TabIndex = 1;
            this.radKhac.Text = "Không biên chế";
            // 
            // radBC
            // 
            this.radBC.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radBC.ForeColor = System.Drawing.Color.DarkBlue;
            this.radBC.Location = new System.Drawing.Point(14, 11);
            this.radBC.Name = "radBC";
            this.radBC.Size = new System.Drawing.Size(82, 24);
            this.radBC.TabIndex = 0;
            this.radBC.Text = "Biên chế";
            // 
            // btn_exel
            // 
            this.btn_exel.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_exel.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_exel.Image = global::Human.Properties.Resources.white_excel;
            this.btn_exel.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_exel.Location = new System.Drawing.Point(91, 144);
            this.btn_exel.Name = "btn_exel";
            this.btn_exel.Size = new System.Drawing.Size(60, 28);
            this.btn_exel.TabIndex = 9;
            this.btn_exel.Text = "&Excel";
            this.btn_exel.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_exel.Click += new System.EventHandler(this.btn_exel_Click);
            // 
            // frm_bangchamcong
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 14);
            this.ClientSize = new System.Drawing.Size(284, 184);
            this.Controls.Add(this.btn_exel);
            this.Controls.Add(this.groupBox1);
            this.Controls.Add(this.cbobophan);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.txtnam);
            this.Controls.Add(this.txtthang);
            this.Controls.Add(this.btn_thoat);
            this.Controls.Add(this.btn_chon);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.label1);
            this.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.ForeColor = System.Drawing.Color.Navy;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frm_bangchamcong";
            this.ShowInTaskbar = false;
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Bảng tổng hợp chấm công";
            this.Load += new System.EventHandler(this.Baocao_chamcong_Load);
            this.groupBox1.ResumeLayout(false);
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void txtthang_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}

		private void txtnam_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}

		private void Baocao_chamcong_Load(object sender, System.EventArgs e)
		{           
            try
			{
                ngay_ht = DateTime.Now.Day.ToString().PadLeft(2, '0');
				txtthang.Text =DateTime.Now.Month.ToString();
				txtnam.Text =DateTime.Now.Year.ToString();
				thang =txtthang.Text.Trim().PadLeft(2,'0');
				nam =txtnam.Text.Trim();
                s_mmyy = thang + nam.Substring(2, 2);
				Load_bophan();
			}
			catch{}
           
        }
		private void Load_bophan()
		{
			cbobophan.DisplayMember="tenbp";
			cbobophan.ValueMember="madv";
			cbobophan.DataSource =mDB.get_bophan().Tables[0];
			cbobophan.SelectedIndex =-1;
		}
		private bool ktra(string a)
		{
			int i=0;
			try
			{
				i =int.Parse(a.Trim());
				return true;
			}
			catch
			{
				return false;
			}
		}
		private bool Kiemtra(string tn)
		{
			try
			{
				DataSet ds =new DataSet();
				ds =mDB.get_data("select * from bcc"+tn+"");
				return ds.Tables[0].Rows.Count>0;
			}
			catch
			{
				return false;
			}
		}
        private string d_ngay(string str)
        {
            string s = "";
            if (str.Trim().Length > 0)
            {
                string[] s1 = str.Split(',');
                for (int i = 0; i < s1.Length; i++)
                {
                    if (s1[i].ToString().Trim().IndexOf("2") == -1)
                        s = s + s1[i].ToString().Trim() + ",";
                }
            }
            return s;
        }
        private string n_ngay(string str)
        {
            string s = "";
            if (str.Trim().Length > 0)
            {
                string[] s1 = str.Split(',');
                for (int i = 0; i < s1.Length; i++)
                {
                    if (s1[i].ToString().Trim().IndexOf("2") != -1)
                        s = s + s1[i].ToString().Trim() + ",";
                }
            }
            return s;
        }
        DataSet ds = new DataSet();
        private void print(int prn)
        {
            DataSet dstmp = new DataSet();   
            ExportToExcel exp = new ExportToExcel();
            ds= get_datacc();
            dscctt=get_datacctt();
            for (int i = 1; i <= 31; i++)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    DataRow[] dr_t = dscctt.Tables[0].Select("manv='" + dr["manv"].ToString().Trim() + "'");
                    if (dr_t.Length > 0)
                    {
                        if (dr_t[0]["mn" + i.ToString() + ""].ToString().Trim() != "")
                        {
                            dr["mn" + i.ToString().Trim() + ""] = dr_t[0]["mn" + i.ToString() + ""].ToString().Trim();
                        }                        
                    }
                }
            }
            ds.WriteXml("..\\xml\\h_bangchamcong.xml", XmlWriteMode.WriteSchema);
            if (ds.Tables[0].Rows.Count > 0)
            {
                string tenreport = "h_Bangchamcong.rpt";
                switch (prn)
                {
                    case 1: frmReport f = new frmReport(mDB, ds, "", tenreport, true, 0,user_dn);
                        f.ShowDialog();
                        break;
                    case 2: dstmp = exp_exel(ds);
                        exp.exp_excel_bangcc(dstmp, "bangchamcong_" + s_mmyy, thang, nam);
                        break;
                }
            }
            
        }
  
        private DataSet get_datacc()
        {
                     
            string sql = "",sql2="", ngay = "", mn = "", fields_cl = "", fields_kl = "", fields_bh = "", fields_kh = "", user = "", s_mmyy = "";
            string f_bh = "", f_kl = "", f_cl = "", f_kh = "";
            string sd_bh = "", sd_kl = "", sd_cl = "", sd_kh = "", sn_bh = "", sn_kl = "", sn_cl = "", sn_kh = "";
            int mm = 0;
            user = mDB.user;
            string sql1 = " select case when nhombc='BH' then ma else null end as bh";
            sql1 += " ,case when nhombc='KL' then ma else null end as kl";
            sql1 += " ,case when nhombc='CL' then ma else null end as cl";
            sql1 += " ,case when nhombc='KH' then ma else null end as kh from "+user+".dmlydo_phep";
            DataSet ds_ld = mDB.get_data(sql1);

            for (int i = 0; i < ds_ld.Tables[0].Rows.Count; i++)
            {
                if (ds_ld.Tables[0].Rows[i]["kl"].ToString().Trim() != "")
                {
                    f_kl += " '" + ds_ld.Tables[0].Rows[i]["kl"].ToString().Trim() + "',";
                }
                if (ds_ld.Tables[0].Rows[i]["cl"].ToString().Trim() != "")
                {
                    f_cl += " '" + ds_ld.Tables[0].Rows[i]["cl"].ToString().Trim() + "',";
                }
                if (ds_ld.Tables[0].Rows[i]["bh"].ToString().Trim() != "")
                {
                    f_bh += " '" + ds_ld.Tables[0].Rows[i]["bh"].ToString().Trim() + "',";
                }
                if (ds_ld.Tables[0].Rows[i]["kh"].ToString().Trim() != "")
                {
                    f_kh += " '" + ds_ld.Tables[0].Rows[i]["kh"].ToString().Trim() + "',";
                }
            }
            // lay danh sach nhom ly do phep theo nua ngay va du ngay
            sd_bh = f_bh.Length > 0 ? d_ngay(f_bh.Substring(0, f_bh.Length - 1)) : "";
            sd_kl = f_kl.Length > 0 ? d_ngay(f_kl.Substring(0, f_kl.Length - 1)) : "";
            sd_cl = f_cl.Length > 0 ? d_ngay(f_cl.Substring(0, f_cl.Length - 1)) : "";
            sd_kh = f_kh.Length > 0 ? d_ngay(f_kh.Substring(0, f_kh.Length - 1)) : "";
            //
            sn_bh = f_bh.Length > 0 ? n_ngay(f_bh.Substring(0, f_bh.Length - 1)) : "";
            sn_kl = f_kl.Length > 0 ? n_ngay(f_kl.Substring(0, f_kl.Length - 1)) : "";
            sn_cl = f_cl.Length > 0 ? n_ngay(f_cl.Substring(0, f_cl.Length - 1)) : "";
            sn_kh = f_kh.Length > 0 ? n_ngay(f_kh.Substring(0, f_kh.Length - 1)) : "";

            s_mmyy = thang + nam.Substring(2, 2);
            
            mm = int.Parse(thang) + 1;
            int m_songay = 0;
            m_songay = 31;
            for (int i = 1; i <= m_songay; i++)
            {
                ngay = "n" + i.ToString();
                mn += " trim(upper(" + ngay + "))  as m" + ngay + ", ";
                fields_cl += (sd_cl.Length > 0 && sn_cl.Length > 0) ? " case when trim(upper(" + ngay + ")) in (" + sd_cl.Substring(0, sd_cl.Length - 1) + ") then 1 when trim(upper(" + ngay + ")) in (" + sn_cl.Substring(0, sn_cl.Length - 1) + ") then 1 when trim(upper(" + ngay + ")) in (" + sn_kl.Substring(0, sn_kl.Length - 1) + "," + sn_bh.Substring(0, sn_bh.Length - 1) + "," + sn_kh.Substring(0, sn_kh.Length - 1) + ") then 0.5 else 0 end as t_cl" + i.ToString() + ", " : "";
                fields_kl += (sd_kl.Length > 0 && sn_kl.Length > 0) ? " case when trim(upper(" + ngay + ")) in (" + sd_kl.Substring(0, sd_kl.Length - 1) + ") then 1 when trim(upper(" + ngay + ")) in (" + sn_kl.Substring(0, sn_kl.Length - 1) + ") then 0.5 else 0 end as t_kl" + i.ToString() + ", " : "";
                fields_bh += (sd_bh.Length > 0 && sn_bh.Length > 0) ? " case when trim(upper(" + ngay + ")) in (" + sd_bh.Substring(0, sd_bh.Length - 1) + ") then 1 when trim(upper(" + ngay + ")) in (" + sn_bh.Substring(0, sn_bh.Length - 1) + ") then 0.5 else 0 end as t_bh" + i.ToString() + ", " : "";
                fields_kh += (sd_kh.Length > 0 && sn_kh.Length > 0) ? " case when trim(upper(" + ngay + ")) in (" + sd_kh.Substring(0, sd_kh.Length - 1) + ") then 1 when trim(upper(" + ngay + ")) in (" + sn_kh.Substring(0, sn_kh.Length - 1) + ") then 0.5 else 0 end as t_kh" + i.ToString() + ", " : "";
            }
            
            sql = " select '" + thang + "' as thang, '" + nam + "' as nam,hd.macv,a.manv,bp.madv,bp.tenbp as bophan,trim(a.ho)||' '||trim(a.ten) as hoten,c.ten,";
            if (radBC.Checked == true)
                sql += " 'Biên chế' as labl,";
            else
                if (radKhac.Checked == true)
                    sql += " 'Ngoài biên chế' as labl,";
                else
                    sql += " ' ' as labl,";
            mn = mn.Trim();
            fields_cl = fields_cl.Trim();
            fields_kl = fields_kl.Trim();
            fields_bh = fields_bh.Trim();
            fields_kh = fields_kh.Trim();
            sql += mn.Substring(0, mn.Length - 1);
            sql += fields_cl != "" ? "," + fields_cl.Substring(0, fields_cl.Length - 1) : "";
            sql += fields_kl != "" ? "," + fields_kl.Substring(0, fields_kl.Length - 1) : "";
            sql += fields_bh != "" ? "," + fields_bh.Substring(0, fields_bh.Length - 1) : "";
            sql += fields_kh != "" ? "," + fields_kh.Substring(0, fields_kh.Length - 1) : "";
            sql += " ,0.0 as cluong,0.0 as kluong,0.0 as bhiem, 0.0 as khac";
            sql += " from " + user + ".llnv a inner join " + user + ".hopdong hd on a.manv=hd.manv ";
            sql += " inner join " + user + ".chamcong_" + s_mmyy + " b on a.manv=b.manv";
            sql += " left join " + user + ".dmcd c on a.macd=c.ma ";
            sql += " left join " + user + ".bophan bp on bp.madv=b.madv";
            sql += " inner join " + user + ".dmcv cv on hd.macv=cv.macv";
            sql += " inner join " + user + ".mahd mh on hd.loaihd=mh.mahd ";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            sql += " where hd.ngayhd is not null and to_date(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy')<=to_date('01/" + thang + "/" + nam + "','dd/mm/yyyy') and b.mmyy='" + s_mmyy + "'";
            if (cbobophan.SelectedIndex > -1)
            {
                sql += "  and b.madv='" + cbobophan.SelectedValue.ToString().Trim() + "' ";
            }
            if (radBC.Checked == true)
                sql += " and mh.nhombc = 1";
            else
                if (radKhac.Checked == true)
                    sql += " and mh.nhombc <> 1";
            sql += " and hd.lan=ml.lan";
            sql += " order by bp.madv,cv.stt,c.thutu,a.manv";
            sql2 = " select * from "+user+".dmlydo_phep order by stt";
            ds = mDB.get_data(sql,sql2);
            
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    decimal cluong = 0, kluong = 0, bhiem = 0, khac = 0;
                    for (int i = 1; i <= 31; i++)
                    {
                        string ngay_1 = "t_cl" + i.ToString(), k_l = "t_kl" + i.ToString(), b_h = "t_bh" + i.ToString(), k_h = "t_kh" + i.ToString();
                        cluong += decimal.Parse(dr[ngay_1].ToString());
                        kluong += decimal.Parse(dr[k_l].ToString());
                        bhiem += decimal.Parse(dr[b_h].ToString());
                        khac += decimal.Parse(dr[k_h].ToString());
                        string str = dr["mn" + i.ToString() + ""].ToString();
                        if (str.Trim() != "")
                        {
                            DataRow[] r = ds.Tables[1].Select("ma = '" + str.Trim() + "' ");
                            if (r.Length > 0)
                            {
                                dr["mn" + i.ToString() + ""] = r[0]["kihieu_phep"].ToString();
                            }
                            else
                            {
                                dr["mn" + i.ToString() + ""] = "";
                            }
                        }
                        else
                        {
                            dr["mn" + i.ToString() + ""] = "";
                        }
                    }
                    dr["cluong"] = cluong.ToString();
                    dr["kluong"] = kluong.ToString();
                    dr["bhiem"] = bhiem.ToString();
                    dr["khac"] = khac.ToString();
                }            
            }
            return ds;
        }
        private DataSet get_datacctt()
        {
            thang = txtthang.Text.Trim().PadLeft(2, '0');
            nam = txtnam.Text.Trim();
            s_mmyy = thang + nam.Substring(2, 2);
            string sql = "",sql2="", ngay = "", chamcong_tt = "";
            s_mmyy = thang + nam.Substring(2, 2);
            for (int i = 1; i <= 31; i++)
            {
                ngay = "n" + i.ToString();
                chamcong_tt += " case when trim(upper(" + ngay + ")) <> '' then trim(upper(" + ngay + "))  else '' end as m" + ngay + ", ";
            }
            sql = " select '" + thang + "' as thang, '" + nam + "' as nam,hd.macv,a.manv,bp.madv,bp.tenbp as bophan,trim(a.ho)||' '||trim(a.ten) as hoten,c.ten,";
            if (radBC.Checked == true)
                sql += " 'Biên chế' as labl,";
            else
                if (radKhac.Checked == true)
                    sql += " 'Ngoài biên chế' as labl,";
                else
                    sql += " ' ' as labl,";
            chamcong_tt = chamcong_tt.Trim();
            sql += chamcong_tt.Substring(0, chamcong_tt.Length - 1);
            sql += " , 0 as tt,0 as tn,0 as tl,0 as ht,0 as hn,0 as hl,bp.sothutu as stt_bp,cv.stt as stt_cv,c.thutu as stt_cd";
            sql += " from " + mDB.user + ".llnv as a inner join " + mDB.user + ".hopdong as hd on a.manv=hd.manv ";
            sql += " inner join " + mDB.user + ".chamcong_thuongtruc as b on a.manv=b.manv";
            sql += " left join " + mDB.user + ".dmcd as c on a.macd=c.ma";
            sql += " left join " + mDB.user + ".bophan as bp on bp.madv=b.madv";
            sql += " inner join " + mDB.user + ".dmcv as cv on hd.macv=cv.macv";
            sql += " inner join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv";
            sql += " where hd.ngayhd is not null and b.mmyy='" + s_mmyy + "'";
            sql += " and to_timestamp(hd.ngaydenct,'yyyy-mm-dd') <=to_timestamp('" + nam + "-" + thang + "-" + ngay_ht + "','yyyy-mm-dd')";
            if (cbobophan.SelectedIndex > -1)
            {
                sql += "  and b.madv='" + cbobophan.SelectedValue.ToString().Trim() + "' ";
            }
            if (radBC.Checked == true)
                sql += " and mh.nhombc=1";
            else
                if (radKhac.Checked == true)
                    sql += " and mh.nhombc <> 1";
            sql += " and hd.lan=ml.lan";
            sql += " order by bp.madv,cv.stt,c.thutu,a.manv";
            sql2 = " select * from " + mDB.user + ".dmlydo_phep where nhombc='TR' order by stt";
            dscctt = mDB.get_data(sql,sql2);
            if (dscctt.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in dscctt.Tables[0].Rows)
                {
                    int tt = 0, tn = 0, tl = 0, ht = 0, hn = 0, hl = 0;
                    for (int i = 1; i <= 31; i++)
                    {
                        if (dr["mn" + i.ToString() + ""].ToString().Trim().ToUpper() == "TT")
                            tt++;
                        if (dr["mn" + i.ToString() + ""].ToString().Trim().ToUpper() == "TN")
                            tn++;
                        if (dr["mn" + i.ToString() + ""].ToString().Trim().ToUpper() == "TL")
                            tl++;
                        if (dr["mn" + i.ToString() + ""].ToString().Trim().ToUpper() == "HT")
                            ht++;
                        if (dr["mn" + i.ToString() + ""].ToString().Trim().ToUpper() == "HN")
                            hn++;
                        if (dr["mn" + i.ToString() + ""].ToString().Trim().ToUpper() == "HL")
                            hl++;
                        string str = dr["mn" + i.ToString() + ""].ToString();
                        if (str.Trim() != "")
                        {
                            DataRow[] r = dscctt.Tables[1].Select("ma = '" + str.Trim() + "' ");
                            if (r.Length > 0)
                            {
                                dr["mn" + i.ToString() + ""] = r[0]["kihieu_phep"].ToString();
                            }
                            else
                            {
                                dr["mn" + i.ToString() + ""] = "";
                            }
                        }
                        else
                        {
                            dr["mn" + i.ToString() + ""] = "";
                        }
                    }
                    dr["tt"] = tt.ToString();
                    dr["tn"] = tn.ToString();
                    dr["tl"] = tl.ToString();
                    dr["ht"] = ht.ToString();
                    dr["hn"] = hn.ToString();
                    dr["hl"] = hl.ToString();
                }
            }
            else
            {
                MessageBox.Show(lan.Change_language_MessageText("Chưa có dữ liệu"),lan.Change_language_MessageText( "Thông báo"), MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            return dscctt;
        }
        
        private void tonghop()
        {
            
        }

        private DataSet exp_exel(DataSet dse)
        {
            DataSet dse1 = new DataSet();
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("Hoten", typeof(string));
            string []ngay=new string[] {"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19","20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"};
            for(int i=0;i<ngay.Length;i++)
            {
                dse1.Tables[0].Columns.Add(ngay[i], typeof(string));
            }
            dse1.Tables[0].Columns.Add("tongso", typeof(string));
            dse1.Tables[0].Columns.Add("ts_lydo", typeof(string));        
            

            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables[0].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt;
                nrow["Hoten"] = r["Hoten"].ToString();
                for (int i = 1; i <= ngay.Length; i++)
                {
                    string n=i.ToString().PadLeft(2,'0');
                    string m="mn"+i.ToString();
                    nrow[n] = r[m].ToString();
                }
                nrow["tongso"] = r["tongso"].ToString();
                nrow["ts_lydo"] = r["ts_lydo"].ToString();
                dse1.Tables[0].Rows.Add(nrow);
            }
            return dse1;
        }

        private void btn_exel_Click(object sender, EventArgs e)
        {
            if (!ktra(txtthang.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Tháng không hợp lệ !"),lan.Change_language_MessageText( "Thông báo"));
                txtthang.Focus();
                return;
            }
            if (!ktra(txtnam.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Năm không hợp lệ !"),lan.Change_language_MessageText( "Thông báo"));
                txtnam.Focus();
                return;
            }
            thang = txtthang.Text.Trim().PadLeft(2, '0');
            nam = txtnam.Text.Trim();
            if (!mDB.bMmyy(thang + nam.Substring(2, 2)))
            {
                MessageBox.Show(lan.Change_language_MessageText("Số liệu chưa tạo !"),lan.Change_language_MessageText( "Thông báo"));
                return;
            }
            print(2);
            btn_thoat.Focus();
        }

        private void btn_chon_Click(object sender, EventArgs e)
        {
            if (!ktra(txtthang.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Tháng không hợp lệ !"),lan.Change_language_MessageText( "Thông báo"));
                txtthang.Focus();
                return;
            }
            if (!ktra(txtnam.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Năm không hợp lệ !"),lan.Change_language_MessageText( "Thông báo"));
                txtnam.Focus();
                return;
            }
            thang = txtthang.Text.Trim().PadLeft(2, '0');
            nam = txtnam.Text.Trim();
            if (!mDB.bMmyy(thang + nam.Substring(2, 2)))
            {
                MessageBox.Show(lan.Change_language_MessageText("Số liệu chưa tạo !"),lan.Change_language_MessageText( "Thông báo"));
                return;
            }
            print(1);
            btn_exel.Focus();
        }

        private void btn_thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }      
	}
}
